package com.atguigu.preparedstatement.crud;

import com.atguigu.util.JDBCUtils;
import org.junit.Test;

import java.io.InputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Properties;

/**
 * 使用 PreparedStatement 来代替 Statement，实现对数据库的增删改操作
 * <p>
 * 增删改：查
 *
 * @author admin
 */
public class PreparedStatementUpdateTest {

    @Test
    public void testCommUpdate() throws Exception {

        // String sql = "delete from customers where id = ?";
        // update(sql, 3);

        String sql = "update `order` set order_name = ? where order_id = ?";
        update(sql, "DD", 2);

    }

    /**
     * 通用的增删改操作
     * sql 中占位符的个数与可变形参的长度相同！
     */
    public void update(String sql, Object... args) throws Exception {

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            // 1.获取数据库连接
            connection = JDBCUtils.getConnection();

            // 2.预编译 sql 语句，返回 PreparedStatement 实例对象
            preparedStatement = connection.prepareStatement(sql);

            // 3.填充占位符  小心参数声明错误！！！
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            // 4.执行
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.资源的关闭
            JDBCUtils.closeResource(connection, preparedStatement);
        }
    }

    /**
     * 修改 customers 表中的一条记录
     */
    @Test
    public void testUpdate() {

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            // 1.获取数据库连接
            connection = JDBCUtils.getConnection();

            // 2.预编译 sql 语句，返回 PreparedStatement 实例对象
            String sql = "update customers set name = ? where id = ?";
            preparedStatement = connection.prepareStatement(sql);

            // 3.填充占位符
            preparedStatement.setObject(1, "莫扎特");
            preparedStatement.setObject(2, 18);

            // 4.执行
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.资源的关闭
            JDBCUtils.closeResource(connection, preparedStatement);
        }
    }

    /**
     * 向 customers 表中添加一条记录
     */
    @Test
    public void testInsert() {

        Connection connection = null;
        PreparedStatement preparedStatement = null;


        try {
            // 1.读取配置文件中的4个基本信息
            InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(inputStream);

            String driver = properties.getProperty("driver");
            String url = properties.getProperty("url");
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");

            // 2.加载驱动
            Class.forName(driver);

            // 3.获取连接
            connection = DriverManager.getConnection(url, user, password);

            // 4.预编译 sql 语句，返回 PreparedStatement 的实例    ?：占位符
            String sql = "insert into customers(name, email, birth) value (?, ?, ?)";
            preparedStatement = connection.prepareStatement(sql);

            // 填充占位符
            preparedStatement.setString(1, "哪吒");
            preparedStatement.setString(2, "nezha@gmail.com");
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("YYYY-MM-DD");
            java.util.Date date = simpleDateFormat.parse("1000-01-01");
            preparedStatement.setDate(3, new Date(date.getTime()));

            // 6.执行操作
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 7.关闭资源
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
}
